USE SoftUni
GO

IF OBJECT_ID (N'fn_unionEmployeesFirstNameAndTownsName', N'TF') IS NOT NULL -- FN = Scalar Function, IF = Inlined Table Function, TF = Table Function
BEGIN
	DROP FUNCTION fn_unionEmployeesFirstNameAndTownsName;
END
GO

CREATE FUNCTION fn_unionEmployeesFirstNameAndTownsName()
	RETURNS @tbl_extractedNamesAndTowns TABLE 
		(
			extractedNameOrTown NVARCHAR(30)
		)
	AS
	BEGIN
		INSERT INTO @tbl_extractedNamesAndTowns
			SELECT e.FirstName
				FROM Employees e
				UNION
					SELECT t.Name
						FROM Towns t
		RETURN
	END
GO

IF OBJECT_ID (N'fn_matchLettersSetWithWord', N'FN') IS NOT NULL -- FN = Scalar Function, IF = Inlined Table Function, TF = Table Function
BEGIN
	DROP FUNCTION fn_matchLettersSetWithWord;
END
GO

CREATE FUNCTION fn_matchLettersSetWithWord(@letterSet NVARCHAR(20), @word NVARCHAR(20))
	RETURNS INT
BEGIN
	DECLARE @result INT = 1
	DECLARE @letterSetLength INT = LEN(@letterSet)
	DECLARE @i INT = 1
	
	WHILE @i <= @letterSetLength
	BEGIN
		DECLARE @currentChar NVARCHAR(1) = SUBSTRING(@letterSet, @i, 1)

		IF CHARINDEX(@currentChar, @word
			COLLATE Latin1_General_CI_AS) = 0 -- CI -> case-insensitive
		BEGIN
			SET @result = 0

			BREAK
		END

		SET @i = @i + 1
	END

	RETURN @result
END
GO

DECLARE @sampleLetterSet NVARCHAR(1000) = 'sofia'

SELECT * FROM fn_unionEmployeesFirstNameAndTownsName() value
	WHERE dbo.fn_matchLettersSetWithWord(@sampleLetterSet, value.extractedNameOrTown) = 1
GO